home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Agent Central Host Computer
/
Agent - Central Host Computer.iso
/
_SETUP.1
/
vmagsales.sql
< prev
next >
Wrap
Text File
|
2000-05-12
|
1KB
|
42 lines
/*
This view collects sales and issues of magnetic stripe cards: both passes and SVCs.
*/
create or replace view vmagsales as
SELECT
WAYSVC.FAREBOX_GLID, trunc(WAYSVC.CONV_DATE) Rpt_day,
-1 DVAR, (WAYSVC.NewVAL - WAYSVC.OldVAL) Net_$,
DRI.ROUTE_ID, DRI.RUN_ID, DRI.TRIP_ID, DRI.DRIVER_CONTEXT_ID,
far.fare_context_id, ope.op_context_id,
WAYSVC.DET_SEQ_NUM
FROM
WAYSVC WAYSVC,
MSTRREC MSTRREC,
DRIVER_CONTEXT DRI,
fare_context far,
operator_context ope
WHERE
( WAYSVC.DET_SEQ_NUM=MSTRREC.DET_SEQ_NUM ) AND
( MSTRREC.DRIVER_CONTEXT=DRI.DRIVER_CONTEXT_ID ) and
( MSTRREC.fare_CONTEXT=far.fare_CONTEXT_ID ) and
( MSTRREC.operator_CONTEXT=ope.op_cONTEXT_ID )
union all
SELECT
WAYISSUE.FAREBOX_GLID, trunc(WAYISSUE.CONV_DATE) Rpt_day,
WAYISSUE.DVARIABLE dvar, WAYISSUE.CARD_VAL Card_$,
DRI.ROUTE_ID, DRI.RUN_ID,
DRI.TRIP_ID, DRI.DRIVER_CONTEXT_ID,
far.fare_context_id, ope.op_context_id,
WAYISSUE.DET_SEQ_NUM
FROM
WAYISSUE WAYISSUE,
MSTRREC MSTRREC,
DRIVER_CONTEXT DRI,
fare_context far,
operator_context ope
WHERE
( WAYISSUE.DET_SEQ_NUM=MSTRREC.DET_SEQ_NUM ) AND
( MSTRREC.DRIVER_CONTEXT=DRI.DRIVER_CONTEXT_ID ) and
( MSTRREC.fare_CONTEXT=far.fare_CONTEXT_ID ) and
( MSTRREC.operator_CONTEXT=ope.op_cONTEXT_ID );